using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Xml.Xsl;
using APJ.Util;
using System.Globalization;

namespace MTRC.ODMS.Web.SOM.CodeSetup
{
    /// <summary>
    /// Author: XiaoChunhu
    /// DateTime: 2008-11-4
    /// upload the Excel or download data from database
    /// </summary>
    public partial class CQAContractorStaffUpload : BasePage
    {
        #region MenuID

        protected override string MenuID
        {
            get
            {
                return "M140817";//set the Menu
            }
        }

        #endregion

        #region Page Init And Load

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            this.ChangeLanguage += new ChangeLanguageEventHandler(SetLanguage_Click);
        }

        #endregion

        #region page load and set the page lanuage

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                SetLanguage();
            }
        }

        /// <summary>
        /// set the lanuage for the page
        /// </summary>
        private void SetLanguage()
        {
            this.lblDownloadData.Text = this.GetLabel("SOM_DownloadTheCQAContractorSatff");
            this.lblFileUpload.Text = this.GetLabel("SOM_FileToBeUploaded", true);
            this.lblUploadNote.Text = this.GetLabel("SOM_Note");
            this.btnDownloadData.Text = this.GetLabel("SOM_DownloadDown");
            this.btnDownTemplte.Text = this.GetLabel("SOM_DownloadEmptyTemplate");
            this.btnUpload.Text = this.GetLabel("SOM_Upload");
            this.lblTitle.Text = this.GetLabel("SOM_StaffTitle");//wyd
            //BindData();
        }

        void SetLanguage_Click(object sender, EventArgs e)
        {
            SetLanguage();
        }

        #endregion

        #region Bind Some Data

        /// <summary>
        /// get the datatable and bind on the grvData
        /// </summary>
        //private void BindData()
        //{
        //    //bind the data on the grvData
        //    this.grvData.DataSource = MTRC.ODMS.DAL.SOM.CodeSetup.CQAContractorStaffUpload.GetAllMessage(this.LangCode).DefaultView;
        //    this.grvData.DataBind();
        //}

        /// <summary>
        /// where you trun page,this function will be run 
        /// </summary>
        //protected void grvData_NeedDataBind()
        //{
        //    BindData();
        //}

        #endregion

        #region Some Event

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            #region upload Excel

            string fileName = "CQAContractiorStaff.xls";//set the file name
            bool hasERROR = false;
            if (this.fileUpload.PostedFile.ContentLength <= 0)//get the length of the file
            {
                this.Alert(this.GetMessage("SOM_FilePathNull"));
                return;
            }

            //Add by LiangZehong on 2008-12-18, for issue 0634 Begin...
            string fileType = string.Empty;
            fileType = this.fileUpload.PostedFile.FileName.Substring(this.fileUpload.PostedFile.FileName.LastIndexOf(".") + 1);
            if (fileType.ToLower() != ("xls").ToLower())
            {
                this.Alert(this.GetMessage("SOM_FileTypeError"));
                return;
            }
            //End...

            XmlDocument xdoc = new XmlDocument();
            try
            {
                xdoc.Load(this.fileUpload.PostedFile.InputStream);
                //----fixed issue 0633
                if (xdoc.GetElementsByTagName("Worksheet").Count > 1)
                {
                    Alert(GetMessage("SOM_OneSheetOnly"));
                    return;
                }
                //------

                //xdoc.Save(@"C:\hrm.xml");			//for debug
                string xpath = "ss:Workbook/ss:Worksheet/ss:Table/ss:Row[not (@ss:Hidden)]";
                System.Xml.XmlNamespaceManager xnm = new XmlNamespaceManager(xdoc.NameTable);

                xnm.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");

                XmlNodeList nodelist = xdoc.SelectNodes(xpath, xnm);
                int i = 0;
                XmlDocument xml = new XmlDocument();
                xml.LoadXml("<root/>");

                #region Set XML 

                foreach (XmlNode node in nodelist)
                {
                    if (i > 0)		// first row is head
                    {
                        string StaffID = "",
                            PreferName = "",
                            ChineseName = "",
                            StaffName = "",
                            EmailAddress = "",
                            Tel = "",
                            OfficeTel = "",
                            FaxPhone = "",
                            Status = "",
                            LocationCode = "",
                            LocationDesc = "",
                            JobTitle = "",
                            GradeCode = "",
                            JobDesc = "",
                            JobCode = "",
                            CQAChk = "",
                            Address1 = "",
                            Address2 = "",
                            Address3 = "",
                            FlagObsolete = "",
                            LocationID="",
                            JoinDate="" ,
                            ObsoleteDate="",
                            BirthDate="";
                        try
                        {
                            for (int j = 0, k = 0; j < node.ChildNodes.Count; j++)
                            {
                                if (node.ChildNodes[j].Attributes["ss:Index"] != null)
                                {
                                    int h = 0;
                                    if (int.TryParse(node.ChildNodes[j].Attributes["ss:Index"].Value, out h))
                                    {
                                        k = h - 1;
                                    }
                                }

                            #region Get Data from Excel

                                switch (k)
                                {
                                    case 0:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            StaffID = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 1:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                             PreferName = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                            break;
                                    case 2:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                             ChineseName = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                            break;
                                    case 3:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                StaffName = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                            break;
                                    case 4:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                EmailAddress = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                            break;
                                    case 5:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                Tel = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 6:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                OfficeTel = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 7:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                FaxPhone = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 8:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            JoinDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 9:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                           BirthDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 10:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                Status = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 11:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                LocationCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 12:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                LocationDesc = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 13:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                JobTitle = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 14:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                GradeCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 15:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                JobDesc = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 16:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                JobCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 17:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                CQAChk = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 18:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                Address1 = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 19:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                Address2 = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 20:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                Address3 = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 21:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                            ObsoleteDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 22:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                                FlagObsolete = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                    case 23:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null)
                                        {
                                           LocationID = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        }
                                        break;
                                }
                                k++;
                            }

                                #endregion

                            #region Check Data

                            if (PreferName == "" && ChineseName == "" && StaffName == "" && EmailAddress == "" && Tel == "" && OfficeTel == "" && FaxPhone == "" && Status == "" && LocationCode == "" && LocationDesc == "" && JobTitle == "" && GradeCode == "" && JobDesc == "" && JobCode == "" && CQAChk == "" && Address1 == "" && Address2 == "" && Address3 == "" && LocationID == "" && JoinDate == "" && BirthDate == "" && ObsoleteDate=="")
                            {
                                continue;
                            }
                            //if ((StaffID == String.Empty) || (FlagObsolete == String.Empty))
                            //{
                            //    Alert(this.GetMessage("SOM_StaffEmpty"));
                            //}
                            //else if (StaffID.Length > 40)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffStaffID"));
                            //}
                            //else if (PreferName.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffPreferName"));
                            //}
                            //else if (ChineseName.Length > 20)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffChineseName"));
                            //}
                            //else if (StaffName.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffStaffName"));
                            //}
                            //else if (EmailAddress.Length > 100)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffEmailAddress"));
                            //}
                            //else if (Tel.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffTel"));
                            //}
                            //else if (OfficeTel.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffOfficeTel"));
                            //}
                            //else if (FaxPhone.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffFaxPhone"));
                            //}
                            //else if (LocationCode.Length > 10)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffLocationCode"));
                            //}
                            //else if (LocationDesc.Length > 30)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffLocationDesc"));
                            //}
                            //else if (JobTitle.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffJobTitle"));
                            //}
                            //else if (GradeCode.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffGradeCode"));
                            //}
                            //else if (JobDesc.Length > 30)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffJobDesc"));
                            //}
                            //else if (JobCode.Length > 6)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffJobCode"));
                            //}
                            //else if (CQAChk.Length > 40)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffCQAChk"));
                            //}
                            //else if (Address1.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffAddress1"));
                            //}
                            //else if (Address2.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffAddress2"));
                            //}
                            //else if (Address3.Length > 50)
                            //{
                            //    Alert(this.GetMessage("SOM_StaffAddress3"));
                            //}
                            //if (Status.Trim() != "A" && Status.Trim() != "T")
                            //{
                            //    Alert(this.GetMessage("SOM_StaffStatus"));
                            //}
                            //if (FlagObsolete.Trim()!="Y"&&FlagObsolete.Trim()!="N")
                            //{
                            //    Alert(this.GetMessage("SOM_StaffVoid"));
                            //}

                            //-------------huangyao fixed issue 0765
                            //bool isAnInteger = false;
                            //int tempInt;
                            //try
                            //{
                            //    tempInt = int.Parse(LocationID);
                            //    isAnInteger = true;
                            //}
                            //catch 
                            //{
                            //    isAnInteger = false;
                            //}
                            //--------------------

                            if (StaffID == String.Empty 
                                || FlagObsolete == String.Empty 
                                || StaffID.Length > 40 
                                || PreferName.Length > 50 
                                || ChineseName.Length > 20
                                || StaffName.Length > 50
                                || EmailAddress.Length > 100
                                || Tel.Length > 50
                                || OfficeTel.Length > 50
                                || FaxPhone.Length > 50
                                || LocationCode.Length > 10
                                || LocationDesc.Length > 30
                                || JobTitle.Length > 50
                                || GradeCode.Length > 50
                                || JobDesc.Length > 30
                                || JobCode.Length > 6
                                || CQAChk.Length > 40
                                || CQAChk.Trim() == String.Empty //for 1061: huangyao 20090310
                                || Address1.Length > 50
                                || Address2.Length > 50
                                || Address3.Length > 50
                                || Status.Trim() != "A" && Status.Trim() != "T"
                                || FlagObsolete.Trim() != "Y" && FlagObsolete.Trim() != "N"
                                || LocationID.Trim() != String.Empty //add by zengshunbo 2009-02-13 SOMS_change_form_20090206_Details.doc
                                )
                            {
                                throw new Exception();
                            }

                            if (JoinDate != string.Empty)
                            {
                                //this.ConvertToDate(JoinDate);
                                //DateTime.ParseExact(refreshDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat);
                                DateTime tempdt = DateTime.ParseExact(JoinDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat); //huangyao
                            }

                            if (BirthDate != string.Empty)
                            {
                                //this.ConvertToDate(BirthDate);
                                DateTime.ParseExact(BirthDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat); //huangyao
                            }

                            if (ObsoleteDate != string.Empty)
                            {
                                //DateTime tempdt = this.ConvertToDate(ObsoleteDate);
                                DateTime.ParseExact(ObsoleteDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat); //huangyao
                            }
                        }
                        catch
                        {
                            i++;
                            hasERROR = true;
                            if (node.ChildNodes[0] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[0], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[1] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[1], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[2] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[2], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[3] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[3], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[4] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[4], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[5] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[5], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[6] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[6], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[7] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[7], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[8] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[8], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[9] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[9], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[10] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[10], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[11] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[11], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[12] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[12], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[13] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[13], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[14] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[14], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[15] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[15], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[16] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[16], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[17] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[17], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[18] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[18], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[19] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[19], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[20] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[20], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[21] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[21], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[22] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[22], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[23] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[23], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            continue;
                        }

                            #endregion

                        XmlNode xnode = APJ.Util.Utility.AddNode(xml.FirstChild, "row", "");
                        APJ.Util.Utility.AddAttribute(xnode, "idx", i.ToString());
                        APJ.Util.Utility.AddNode(xnode, "StaffID", "", StaffID);
                        APJ.Util.Utility.AddNode(xnode, "PreferName", "", PreferName);
                        APJ.Util.Utility.AddNode(xnode, "ChineseName", "", ChineseName);
                        APJ.Util.Utility.AddNode(xnode, "StaffName", "", StaffName);
                        APJ.Util.Utility.AddNode(xnode, "EmailAddress", "", EmailAddress);
                        APJ.Util.Utility.AddNode(xnode, "Tel", "", Tel);
                        APJ.Util.Utility.AddNode(xnode, "OfficeTel", "", OfficeTel);
                        APJ.Util.Utility.AddNode(xnode, "FaxPhone", "", FaxPhone);
                        //APJ.Util.Utility.AddNode(xnode, "JoinDate", "", this.ConvertToDateTime(JoinDate).ToString("dd/MM/yyyy"));
                        //APJ.Util.Utility.AddNode(xnode, "BirthDate", "", this.ConvertToDateTime(BirthDate).ToString("dd/MM/yyyy"));
                        if ( JoinDate.Trim()!=string.Empty )//wyd
                        {
                            JoinDate = this.ConvertToDateTime(JoinDate).ToString("dd/MM/yyyy");
                        }
                        APJ.Util.Utility.AddNode(xnode, "JoinDate", "", JoinDate);
                        if ( BirthDate.Trim()!=string.Empty )
                        {
                            BirthDate = this.ConvertToDateTime(BirthDate).ToString("dd/MM/yyyy");
                        }
                        APJ.Util.Utility.AddNode(xnode, "BirthDate", "", BirthDate);
                        APJ.Util.Utility.AddNode(xnode, "Status", "", Status);
                        APJ.Util.Utility.AddNode(xnode, "LocationCode", "", LocationCode);
                        APJ.Util.Utility.AddNode(xnode, "LocationDesc", "", LocationDesc);
                        APJ.Util.Utility.AddNode(xnode, "JobTitle", "", JobTitle);
                        APJ.Util.Utility.AddNode(xnode, "GradeCode", "", GradeCode);
                        APJ.Util.Utility.AddNode(xnode, "JobDesc", "", JobDesc);
                        APJ.Util.Utility.AddNode(xnode, "JobCode", "", JobCode);
                        APJ.Util.Utility.AddNode(xnode, "CQAChk", "", CQAChk);
                        APJ.Util.Utility.AddNode(xnode, "Address1", "", Address1);
                        APJ.Util.Utility.AddNode(xnode, "Address2", "", Address2);
                        APJ.Util.Utility.AddNode(xnode, "Address3", "", Address3);
                        //APJ.Util.Utility.AddNode(xnode, "ObsoleteDate", "", this.ConvertToDateTime(ObsoleteDate).ToString("dd/MM/yyyy"));
                        if ( ObsoleteDate.Trim()!=string.Empty )
                        {
                            ObsoleteDate = this.ConvertToDateTime(ObsoleteDate).ToString("dd/MM/yyyy");
                        }
                        APJ.Util.Utility.AddNode(xnode, "ObsoleteDate", "", ObsoleteDate);
                        APJ.Util.Utility.AddNode(xnode, "FlagObsolete", "", FlagObsolete);
                        APJ.Util.Utility.AddNode(xnode, "LocationID", "", LocationID);
                    }
                    i++;
                }

                #endregion

            #endregion

            #region show the Excel for the error

                //xml.Save(@"C:\result.xml");
                bool successful = false;
                DataSet dsReturn = MTRC.ODMS.DAL.SOM.CodeSetup.CQAContractorStaffUpload.ContractorStaffUpload(this.ODMUser.ID, this.ODMUser.UserID, this.ODMUser.ActingasUserInfo.ID, ref successful, xml);
                if (successful)
                {
                    if ((dsReturn.Tables[0].Rows.Count > 0) || (hasERROR))
                    {
                        string stylePath = "ss:Workbook/ss:Styles";
                        string stylePathS22 = "ss:Workbook/ss:Styles/ss:Style[@ss:ID='ODMS_ExportExcel']";
                        XmlNode nodeS22 = xdoc.SelectSingleNode(stylePathS22, xnm);
                        if (nodeS22 == null)
                        {//not exist style ODMS, create dynamically for highlight
                            XmlNode nodeStyles = xdoc.SelectSingleNode(stylePath, xnm);
                            XmlNode nodeStyle = APJ.Util.Utility.AddNode(nodeStyles, "Style", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeStyle, "ID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            XmlNode nodeInterior = APJ.Util.Utility.AddNode(nodeStyle, "Interior", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior, "Color", "#FF0000", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            APJ.Util.Utility.AddAttribute(nodeInterior, "Pattern", "Solid", "ss", "urn:schemas-microsoft-com:office:spreadsheet");

                            XmlNode nodeStyle2 = APJ.Util.Utility.AddNode(nodeStyles, "Style", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeStyle2, "ID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            XmlNode nodeInterior2 = APJ.Util.Utility.AddNode(nodeStyle2, "Interior", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Color", "#FF0000", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Pattern", "Solid", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            nodeInterior2 = APJ.Util.Utility.AddNode(nodeStyle2, "NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Format", "General Date", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                        }

                        foreach (DataRow row in dsReturn.Tables[0].Rows)
                        {

                            //---------------------huangyao----------
                            XmlNode node = nodelist.Item((int)row["idx"]);
                            for (int j = 0, k = 0; j < node.ChildNodes.Count; j++)
                            {
                                if (node.ChildNodes[j].Attributes["ss:Index"] != null)
                                {
                                    int h = 0;
                                    if (int.TryParse(node.ChildNodes[j].Attributes["ss:Index"].Value, out h))
                                    {
                                        k = h - 1;
                                    }
                                }

                                APJ.Util.Utility.AddAttribute(node.ChildNodes[j], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");

                                k++;
                            }
                            //--------------------------------------

                            //---------------------commentted by huangyao----------
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[0], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[1], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[2], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[3], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[4], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[5], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[6], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[7], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[8], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[9], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[10], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[11], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[12], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[13], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[14], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[15], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[16], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[17], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[18], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[19], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[20], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[21], "StyleID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[22], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[23], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //---------------end------commentted by huangyao----------
                        }
                        Response.Clear();
                        Response.Charset = "UTF-16";
                        Response.ContentEncoding = System.Text.Encoding.GetEncoding(1200);
                        Response.AddHeader("Content-disposition", "attachment;filename=" + fileName);
                        Response.ContentType = "application/vnd.ms-excel";
                        xdoc.Save(Response.OutputStream);
                        xdoc = null;
                        //Alert("Upload file has error record, please checked and upload again!");
                        Response.End();

                    }
                    else
                    {
                        Alert(GetMessage("SOM_UpdateSuccessful"));
                    }
                }
                else
                {
                    xdoc = null;
                    Alert(GetMessage("SOM_UpdateError"));
                }
                xml = null;
            }
            catch //(Exception ex)
            {
                Alert(GetMessage("SOM_UpdateError"));
                //APJ.Util.Log.WindowLog.Log(ex, APJ.Util.Log.LogType.Error, this.ODMUserID);
            }

                #endregion

            finally
            {
                xdoc = null;
            }

        }

        protected void btnDownloadData_Click(object sender, EventArgs e)
        {
            //DataSet ds = MTRC.ODMS.DAL.SOM.CodeSetup.CQAContractorStaffUpload.ContractorStaffDownload();
            //if (ds == null) return;
            //ds.Tables[0].TableName = "ContractorStaffLoad";
            //this.myExportExcel(ds, Context, "ContractorStaffLoad.xls");
            //ds = null;

            #region download data and set the head by langcode

            DataSet ds = MTRC.ODMS.DAL.SOM.CodeSetup.CQAContractorStaffUpload.ContractorStaffDownload(this.LangCode);
            DataTable dtSearchResult = ds.Tables[0];
            dtSearchResult.TableName = this.GetLabel("SOM_StaffTitle");

            //------------------- Modify by zengshunbo 2008-12-11 actionlog(0709)----------------------

            //dtSearchResult.Columns[0].ColumnName = "Staff ID";
            //dtSearchResult.Columns[1].ColumnName = "Prefer Name";
            //dtSearchResult.Columns[2].ColumnName = "Chinese Name";
            //dtSearchResult.Columns[3].ColumnName = "Staff Name";
            //dtSearchResult.Columns[4].ColumnName = "Email Address";
            //dtSearchResult.Columns[5].ColumnName = "Telephone No.";
            //dtSearchResult.Columns[6].ColumnName = "Office telephone No.";
            //dtSearchResult.Columns[7].ColumnName = "Fax No.";
            //dtSearchResult.Columns[8].ColumnName = "JoinDate (dd/mm/yyyy)";
            //dtSearchResult.Columns[9].ColumnName = "Birth Date (dd/mm/yyyy)";
            //dtSearchResult.Columns[10].ColumnName = "Status (A/T) 'A'=Active 'T'=Terminated";
            //dtSearchResult.Columns[11].ColumnName = "Location Code";
            //dtSearchResult.Columns[12].ColumnName = "Location Description";
            //dtSearchResult.Columns[13].ColumnName = "Job Title";
            //dtSearchResult.Columns[14].ColumnName = "Grade Code";
            //dtSearchResult.Columns[15].ColumnName = "Job Description";
            //dtSearchResult.Columns[16].ColumnName = "Job Code";
            //dtSearchResult.Columns[17].ColumnName = "CQA ID";
            //dtSearchResult.Columns[18].ColumnName = "Address part 1";
            //dtSearchResult.Columns[19].ColumnName = "Address part 2";
            //dtSearchResult.Columns[20].ColumnName = "Address part 3";
            //dtSearchResult.Columns[21].ColumnName = "Obsolete Date (dd/mm/yyyy)";
            //dtSearchResult.Columns[22].ColumnName = "Valid (Y/N)";
            //dtSearchResult.Columns[23].ColumnName = "Location (defined in system)";


            dtSearchResult.Columns[0].ColumnName = this.GetLabel("SOM_StaffID", this.LangCode);
            dtSearchResult.Columns[1].ColumnName = this.GetLabel("SOM_PreferName", this.LangCode);
            dtSearchResult.Columns[2].ColumnName = this.GetLabel("SOM_ChineseName", this.LangCode);
            dtSearchResult.Columns[3].ColumnName = this.GetLabel("SOM_StaffName", this.LangCode);
            dtSearchResult.Columns[4].ColumnName = this.GetLabel("SOM_EmailAddress", this.LangCode);
            dtSearchResult.Columns[5].ColumnName = this.GetLabel("SOM_Tel", this.LangCode);
            dtSearchResult.Columns[6].ColumnName = this.GetLabel("SOM_OfficeTel", this.LangCode);
            dtSearchResult.Columns[7].ColumnName = this.GetLabel("SOM_FaxPhone", this.LangCode);
            dtSearchResult.Columns[8].ColumnName = this.GetLabel("SOM_JoinDate", this.LangCode);
            dtSearchResult.Columns[9].ColumnName = this.GetLabel("SOM_BirthDate", this.LangCode);
            dtSearchResult.Columns[10].ColumnName = this.GetLabel("SOM_Status", this.LangCode);
            dtSearchResult.Columns[11].ColumnName = this.GetLabel("SOM_LocationCode", this.LangCode);
            dtSearchResult.Columns[12].ColumnName = this.GetLabel("SOM_LocationDesc", this.LangCode);
            dtSearchResult.Columns[13].ColumnName = this.GetLabel("SOM_JobTitle", this.LangCode);
            dtSearchResult.Columns[14].ColumnName = this.GetLabel("SOM_GradeCode", this.LangCode);
            dtSearchResult.Columns[15].ColumnName = this.GetLabel("SOM_JobDesc", this.LangCode);
            dtSearchResult.Columns[16].ColumnName = this.GetLabel("SOM_JobCode", this.LangCode);
            dtSearchResult.Columns[17].ColumnName = this.GetLabel("SOM_CQAChk", this.LangCode);
            dtSearchResult.Columns[18].ColumnName = this.GetLabel("SOM_Address1", this.LangCode);
            dtSearchResult.Columns[19].ColumnName = this.GetLabel("SOM_Address2", this.LangCode);
            dtSearchResult.Columns[20].ColumnName = this.GetLabel("SOM_Address3", this.LangCode);
            dtSearchResult.Columns[21].ColumnName = this.GetLabel("SOM_ObsoleteDate", this.LangCode);
            dtSearchResult.Columns[22].ColumnName = this.GetLabel("SOM_VoidYesOrNo", this.LangCode);
            dtSearchResult.Columns[23].ColumnName = this.GetLabel("SOM_LocationID", this.LangCode); 

            //-------------------End modify by zengshunbo 2008-12-11 ------------------------------ 

            #endregion

            Common.ExportExcel_showHHMM(ds, Context, "ContractorStaffLoad.xls", "som_upload");
            dtSearchResult = null;
            ds = null;
        }

        #endregion

        protected void btnDownTemplte_Click(object sender, EventArgs e)
        {
            #region set the head

            DataSet ds = new DataSet();
            DataTable dt = new DataTable("ContractorStaffLoad");

            //------------------- Modify by zengshunbo 2008-12-11 actionlog(0709)----------------------

            //dt.Columns.Add("Staff ID");
            //dt.Columns.Add("Prefer Name");
            //dt.Columns.Add("Chinese Name");
            //dt.Columns.Add("Staff Name");
            //dt.Columns.Add("Email Address");
            //dt.Columns.Add("Telephone No.");
            //dt.Columns.Add("Office telephone No.");
            //dt.Columns.Add("Fax No.");
            //dt.Columns.Add("JoinDate (dd/mm/yyyy)");
            //dt.Columns.Add("Birth Date (dd/mm/yyyy)");
            //dt.Columns.Add("Status (A/T) 'A'=Active 'T'=Terminated");
            //dt.Columns.Add("Location Code");
            //dt.Columns.Add("Location Description");
            //dt.Columns.Add("Job Title");
            //dt.Columns.Add("Grade Code");
            //dt.Columns.Add("Job Description");
            //dt.Columns.Add("Job Code");
            //dt.Columns.Add("CQA ID");
            //dt.Columns.Add("Address part 1");
            //dt.Columns.Add("Address part 2");
            //dt.Columns.Add("Address part 3");
            //dt.Columns.Add("Obsolete Date (dd/mm/yyyy)");
            //dt.Columns.Add("Valid (Y/N)");
            //dt.Columns.Add("Location (defined in system)");


            dt.Columns.Add(this.GetLabel("SOM_StaffID", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_PreferName", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_ChineseName", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_StaffName", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_EmailAddress", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_Tel", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_OfficeTel", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_FaxPhone", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_JoinDate", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_BirthDate", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_Status", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_LocationCode", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_LocationDesc", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_JobTitle", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_GradeCode", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_JobDesc", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_JobCode", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_CQAChk", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_Address1", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_Address2", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_Address3", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_ObsoleteDate", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_VoidYesOrNo", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_LocationID", this.LangCode));

            //-------------------End modify by zengshunbo 2008-12-11 ------------------------------ 

            ds.Tables.Add(dt);


            #endregion

            Common.ExportExcel(ds, Context, "ContractorStaffLoad.xls");
        }
    }
}
